package com.studorm.action;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import org.apache.struts2.ServletActionContext;

import com.opensymphony.xwork2.ActionSupport;
import com.studorm.Connection_mysql;
import com.studorm.vo.Student;









public class StudentAction extends ActionSupport {
	
	/**
	 * 
	 */
	//private static final long serialVersionUID = 1L;
	/**
	 * 
	 */
	private static final long serialVersionUID = -3697921271744988026L;
	// 此变量为存放你要读取的表格数据，前台请求后会自动生成JSON格式发送到客户端
	private List<Student> tableRow = new ArrayList<Student>();
	private int amountOfRow = 0;
	private  String stuId;

	//private Pattern patternDeci;
	public List<Student> getTableRow() {
		return tableRow;
	}
	public void setTableRow(List<Student> tableRow) {
		this.tableRow = tableRow;
	}

	public String loadStudent() throws Exception{
		//读取请求的参数 
		
		
		HttpServletRequest request = ServletActionContext.getRequest(); 
		String dormBdName = request.getParameter("dormBdName"); 
		String dormNu = request.getParameter("dormNu");
		
		String rowEachP_t = request.getParameter("rowEachP");//每页行数
		String nuOfP_t = request.getParameter("nuOfP");//要获取第几页的数据
		if(dormBdName == null || dormNu == null ){
			return ERROR;
		}

		int rowEachP = 10; //每页行数
		int nuOfP = 1;//当前是第几页
		if(rowEachP_t != null){
			rowEachP = Integer.parseInt(rowEachP_t);
		}
		if(nuOfP_t != null){
			nuOfP = Integer.parseInt(nuOfP_t);
		}

		Connection conn = Connection_mysql.getConnection();
		PreparedStatement ps;
		ResultSet res;
		String sql;

		
		/* ------------------------
		 * 	此处得到总数据条数，用来计算页数
		 * ------------------------
		 */
		if(dormNu.equals("全部")){
			sql = "SELECT COUNT(*) FROM studormcms.studentmanage WHERE dormBdName=? ";
			ps = conn.prepareStatement(sql);

		}else{
			sql = "SELECT COUNT(*) FROM studormcms.studentmanage WHERE dormBdName=? AND dormNu=?";
			ps = conn.prepareStatement(sql);
			ps.setString(2, dormNu);
		}
		ps.setString(1, dormBdName);
		
		res = ps.executeQuery();
		while(res.next()){
			amountOfRow = res.getInt(1);
		}
		res.close();
		ps.close();
		if(dormNu.equals("全部")){
			sql = "SELECT  studentNu,name, sex, grade, department, major, dormBdName, dormNu,comment FROM studormcms.studentmanage WHERE dormBdName=?  LIMIT ?,?";
			ps = conn.prepareStatement(sql);
			ps.setInt(2, (nuOfP-1) * rowEachP);
			ps.setInt(3, rowEachP);
		}else{
			sql = "SELECT   studentNu,name,sex, grade, department, major,dormBdName, dormNu,comment FROM studormcms.studentmanage WHERE dormBdName=?  AND dormNu=? LIMIT ?,?";
			ps = conn.prepareStatement(sql);
			ps.setString(2, dormNu);
			ps.setInt(3, (nuOfP-1) * rowEachP);
			ps.setInt(4, rowEachP);
		}
		ps.setString(1, dormBdName);
		System.out.println("jjjj");

		
		res = ps.executeQuery();
		while(res.next()){
			Student u = new Student();
		
			
			System.out.println(res.getString("name"));
			u.setStudentNu(res.getString("studentNu"));
			u.setName(res.getString("name"));
			u.setSex(res.getString("sex"));
			u.setGrade(res.getString("grade"));
			u.setDepartment(res.getString("department"));
			u.setMajor(res.getString("major"));
			u.setDormBdName(res.getString("dormBdName"));
			u.setDormNu(res.getString("dormNu"));
			u.setComment(res.getString("comment"));	
			tableRow.add(u);
		}
		res.close();
		ps.close();
		conn.close();

		return SUCCESS;
	}
	public int getAmountOfRow() {
		return amountOfRow;
	}
	public void setAmountOfRow(int amountOfRow) {
		this.amountOfRow = amountOfRow;
	}
	
	/* ---------------------------
	 * 		删除修理数据 ver.12.8
	 * ---------------------------
	 * 
	 * 从前台ajax请求获得delId参数
	 * 此参数为修理记录的ID，通过此ID
	 * 用SQL语句删除数据库中的记录
	 */
	public String delStudent() throws Exception{
		//读取请求的参数 
		HttpServletRequest request = ServletActionContext.getRequest(); 
		String studentId = request.getParameter("delId");
//		int id = 0;
//		if(studentId != null){
//			id = Integer.parseInt(studentId); // 这里不用转换
//		}else{
//			return NONE;
//		}
		Connection conn = Connection_mysql.getConnection();
		String sql = "DELETE FROM studentsinf WHERE studentNu = ?"; //studentNu是char类型
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, studentId); // 这个Int修成String
		int res = ps.executeUpdate();
		ps.close();
		conn.close();
		if(res == 1){
			return SUCCESS;
		}else{
			return NONE;
		}
	}
	
	

	/* -----------------------------
	 * 	只读取一行指定水电费记录 ver.11.24
	 * -----------------------------
	 * 
	 */
	public String loadAStudent() throws Exception{
		HttpServletRequest request = ServletActionContext.getRequest(); 
		String studentId = request.getParameter("studentId");
		Pattern patternNu = Pattern.compile("\\d*");
		if(studentId == null || studentId.isEmpty() || !patternNu.matcher(studentId).matches()){
			return NONE;
		}
		String sql = "SELECT studentNu,name, sex, grade, department, major,dormBdName, dormNu,comment FROM studormcms.studentmanage WHERE studentNu = ?";
		Connection conn = Connection_mysql.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, studentId);
		ResultSet res = ps.executeQuery();	
		while(res.next()){
			Student u = new Student();
			u.setStudentNu(res.getString("studentNu"));
			
			
	
			u.setName(res.getString("name"));
			u.setSex(res.getString("sex"));
			u.setGrade(res.getString("grade"));
			u.setDepartment(res.getString("department"));
			u.setMajor(res.getString("major"));
			u.setDormBdName(res.getString("dormBdName"));
			u.setDormNu(res.getString("dormNu"));
			u.setComment(res.getString("comment"));
			tableRow.add(u);			
		}
		res.close();
		ps.close();
		conn.close();
		
		return SUCCESS;
	}
	
	/* --------------------------------
	 * 		编辑水电费信息 ver.12.13
	 * --------------------------------
	 * 函数中的Pattern用来通过正则表达式验证输入
	 * 的字符串是否符合输入格式
	 * 
	 */
	public String editStudent() throws Exception{
		
		//Calendar rightNow = Calendar.getInstance();
		//SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd  HH:mm:ss");//格式大小写有区别
		//String submitDate = fmt.format(rightNow.getTime());
		
		HttpServletRequest request = ServletActionContext.getRequest(); 
	//	String  dormBdName= request.getParameter("dormBdName");
	//	String dormNu = request.getParameter("dormNu");	
	//	String sex = request.getParameter("sex");
		String studentNu = request.getParameter("studentNu");
	//	String studentId = request.getParameter("studentId");
		String name = request.getParameter("name");	
		String comment = request.getParameter("comment");
	//	String grade = request.getParameter("grade");
	//	String department = request.getParameter("department");
	//	String major = request.getParameter("major");
		//String id = request.getParameter("uid");
        
		Pattern patternNu = Pattern.compile("\\d*");
//		patternDeci = Pattern.compile("\\d*\\.{0,1}\\d*");
		
//		if(id == null || id.isEmpty() || !patternNu.matcher(id).matches()){
//			return NONE;
//		}
		if(studentNu == null || studentNu.isEmpty() || !patternNu.matcher(studentNu).matches()){
			return NONE;
		}
		if(name == null || name.isEmpty() ){
			return NONE;
		}
//		if(sex == null || sex.isEmpty() ){
//			return NONE;
//		}
//		if(grade == null || grade.isEmpty() ){
//			return NONE;
//		}
//		if(department == null || department.isEmpty() ){
//			return NONE;
//		}
//	if(major == null || major.isEmpty()){
//			return NONE;
//		}
//	if(dormNu == null || dormNu.isEmpty()){
//		return NONE;
//	}
	if(comment == null || comment.isEmpty()){
		return NONE;
	}
//	if(dormBdName == null || dormBdName.isEmpty() ){
//		return NONE;
//	}
//	
	
	
		Connection conn = Connection_mysql.getConnection();
		String sql = "UPDATE studentsinf SET name=?,comment=? WHERE studentNu = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		
		ps.setString(3, studentNu);
		ps.setString(1, name);
//		ps.setString(3, sex);
//		ps.setString(4, grade);
//		ps.setString(5, department);
//		ps.setString(6, major);  
//		ps.setString(7, dormNu);
		ps.setString(2, comment);
		//ps.setString(9, dormBdName);  
	//	ps.setString(4, studentId);  
		//ps.setInt(10, Integer.parseInt(id));
		
		int res = ps.executeUpdate();

		ps.close();
		conn.close();
		
		if(res < 1){
			return NONE;
		}else{
			return SUCCESS;
		}
	}
	

	
	
	/* --------------------------------
	 * 		添加水电费信息 ver.11.23
	 * --------------------------------
	 * 函数中的Pattern用来通过正则表达式验证输入
	 * 的字符串是否符合输入格式
	 * 
	 */
	public String addStudent() throws Exception{
		
		//Calendar rightNow = Calendar.getInstance();
		//SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd  HH:mm:ss");//格式大小写有区别
		//String submitDate = fmt.format(rightNow.getTime());

		
		HttpServletRequest request = ServletActionContext.getRequest(); 
		
		
		String studentNu = request.getParameter("studentNu");
		String name = request.getParameter("name");	
		String sex = request.getParameter("sex");
		String major = request.getParameter("major");
		String grade = request.getParameter("grade");
		String dormBdName = request.getParameter("dormBdName");
		String department = request.getParameter("department");
		String dormNu = request.getParameter("dormNu");
		String comment = request.getParameter("comment");
		Pattern patternNu = Pattern.compile("\\d{3}");// 匹配3位整数
		Pattern patternDeci = Pattern.compile("\\d*\\.{0,1}\\d*"); // 匹配小数或整数
		
		if(dormBdName.isEmpty() || dormBdName.equals("请选择")){
			return NONE;
		}
		if(dormNu == null || dormNu.isEmpty() || !patternNu.matcher(dormNu).matches()){
			return NONE;
		}
		if(name == null ||name.isEmpty()){
			return NONE;
		}
		if(studentNu == null ||studentNu.isEmpty()){
			return NONE;
		}
		if(sex == null || sex.isEmpty()){
			return NONE;
		}
		if(grade == null || grade.isEmpty()){
			return NONE;
		}
		if(department == null ||department.isEmpty()){
			return NONE;
		}
	if(major == null || major.isEmpty() || !patternDeci.matcher(major).matches()){
			return NONE;
		}
	

		Connection conn = Connection_mysql.getConnection();
		String sql = "INSERT INTO studentsinf (studentNu, name, sex, grade, department, major,dormBdName, dormNu,comment) VALUES ((SELECT studentNu FROM studentsinf where dormBdName=? and dormNu=?),?,?,?,?,?,?)";
		PreparedStatement ps = conn.prepareStatement(sql);
		
		
		ps.setString(1, studentNu);
		ps.setString(2, name);
		ps.setString(3, sex);
		ps.setString(4, grade);
		ps.setString(5, department);
		ps.setString(6, major);  
		ps.setString(7, dormBdName);
		ps.setString(8, dormNu);
		ps.setString(9,comment);
		ps.setInt(10, 0);
		int res = ps.executeUpdate();

		ps.close();
		conn.close();
		if(res < 1){
			return NONE;
		}else{
			return SUCCESS;
		}
	}
	
	/* -----------------------------
	 * 		修改是否已维修 ver.12.16
	 * -----------------------------
	 * 
	 */
	public String alterComment() throws Exception{
		HttpServletRequest request = ServletActionContext.getRequest(); 
		String id = request.getParameter("alterId");
		String status = request.getParameter("status");
		Pattern patternNu = Pattern.compile("\\d*");
		if(id == null || id.isEmpty() || !patternNu.matcher(id).matches()){
			return NONE;
		}
		String sql = "UPDATE studentsinf SET comment = ? WHERE id=?";
		Connection conn = Connection_mysql.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(2, Integer.parseInt(id));
		if(status.equals("离校")){
			ps.setInt(1, 1);
		}else if(status.equals("在校")){
			ps.setInt(1, 0);
		}else{
			return NONE;
		}
		
		int res = ps.executeUpdate();
		ps.close();
		conn.close();
		if(res > 1){
			return SUCCESS;
		}else{
			return NONE;
		}
	}
	public String loadStuStudent() throws Exception
	{
		ResultSet res;
		HttpServletRequest request = ServletActionContext.getRequest(); 
		HttpSession session = request.getSession();
		String id = (String)session.getAttribute("id");
		System.out.print("4");
//		if(id == null ||id.isEmpty()){		
//			return NONE; WHERE studentNu = Integer.parseInt(id)
//		}
		System.out.print("1");
		String sql = "SELECT  studentNu,name,sex, grade, department, major,dormBdName, dormNu,comment FROM studormcms.studentmanage WHERE studentNu = ?";
//		System.out.print("5");
		Connection conn = Connection_mysql.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, id);
//		System.out.print("6");
		
		res = ps.executeQuery();
//		System.out.print("6");
		while(res.next()){
			Student u = new Student();
		
			System.out.print("7");
			System.out.println(res.getString("name"));
			u.setStudentNu(res.getString("studentNu"));
			u.setName(res.getString("name"));
			u.setSex(res.getString("sex"));
			u.setGrade(res.getString("grade"));
			u.setDepartment(res.getString("department"));
			u.setMajor(res.getString("major"));
			u.setDormBdName(res.getString("dormBdName"));
			u.setDormNu(res.getString("dormNu"));
			u.setComment(res.getString("comment"));	
			tableRow.add(u);
		}
		res.close();
		ps.close();
		conn.close();

		return SUCCESS;
	}
//	public String loadStuStudent() throws Exception{
//		//读取请求的参数 
//		
//		
//		HttpServletRequest request = ServletActionContext.getRequest(); 
//		String dormBdName = request.getParameter("dormBdName"); 
//		String dormNu = request.getParameter("dormNu");
//		
//		String rowEachP_t = request.getParameter("rowEachP");//每页行数
//		String nuOfP_t = request.getParameter("nuOfP");//要获取第几页的数据
//		if(dormBdName == null || dormNu == null ){
//			return ERROR;
//		}
//
//		int rowEachP = 10; //每页行数
//		int nuOfP = 1;//当前是第几页
//		if(rowEachP_t != null){
//			rowEachP = Integer.parseInt(rowEachP_t);
//		}
//		if(nuOfP_t != null){
//			nuOfP = Integer.parseInt(nuOfP_t);
//		}
//
//		Connection conn = Connection_mysql.getConnection();
//		PreparedStatement ps;
//		ResultSet res;
//		String sql;
//
//		
//		/* ------------------------
//		 * 	此处得到总数据条数，用来计算页数
//		 * ------------------------
//		 */
//		if(dormNu.equals("全部")){
//			sql = "SELECT COUNT(*) FROM studormcms.studentmanage WHERE dormBdName=? ";
//			ps = conn.prepareStatement(sql);
//
//		}else{
//			sql = "SELECT COUNT(*) FROM studormcms.studentmanage WHERE dormBdName=? AND dormNu=?";
//			ps = conn.prepareStatement(sql);
//			ps.setString(2, dormNu);
//		}
//		ps.setString(1, dormBdName);
//		
//		res = ps.executeQuery();
//		while(res.next()){
//			amountOfRow = res.getInt(1);
//		}
//		res.close();
//		ps.close();
//		if(dormNu.equals("全部")){
//			sql = "SELECT  studentNu,name, sex, grade, department, major, dormBdName, dormNu,comment FROM studormcms.studentmanage WHERE dormBdName=?  LIMIT ?,?";
//			ps = conn.prepareStatement(sql);
//			ps.setInt(2, (nuOfP-1) * rowEachP);
//			ps.setInt(3, rowEachP);
//		}else{
//			sql = "SELECT   studentNu,name,sex, grade, department, major,dormBdName, dormNu,comment FROM studormcms.studentmanage WHERE dormBdName=?  AND dormNu=? LIMIT ?,?";
//			ps = conn.prepareStatement(sql);
//			ps.setString(2, dormNu);
//			ps.setInt(3, (nuOfP-1) * rowEachP);
//			ps.setInt(4, rowEachP);
//		}
//		ps.setString(1, dormBdName);
//		System.out.println("jjjj");
//
//		
//		res = ps.executeQuery();
//		while(res.next()){
//			Student u = new Student();
//		
//			
//			System.out.println(res.getString("name"));
//			u.setStudentNu(res.getString("studentNu"));
//			u.setName(res.getString("name"));
//			u.setSex(res.getString("sex"));
//			u.setGrade(res.getString("grade"));
//			u.setDepartment(res.getString("department"));
//			u.setMajor(res.getString("major"));
//			u.setDormBdName(res.getString("dormBdName"));
//			u.setDormNu(res.getString("dormNu"));
//			u.setComment(res.getString("comment"));	
//			tableRow.add(u);
//		}
//		res.close();
//		ps.close();
//		conn.close();
//
//		return SUCCESS;
//	}
//	
	public String getStuId() {
		return stuId;
	}
	public void setStuId(String stuId) {
		this.stuId = stuId;
	}
}
